package com.example.dao;

import com.example.domain.Chapter;
import com.example.domain.Novel;
import com.example.domain.Review;
import com.example.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import com.example.domain.OrderRecord;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class NovelDao {
    private JdbcTemplate jdbcTemplate = null;
    String sql = null;
    int affectedRow = 0;
//    根据类型获取小说
    public List<Novel> getTotalCountByType(String type){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels where category=?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Novel.class),type);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    随机获取10本小说
    public List<Novel> getIndexCount(){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels order by rand() limit 12";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Novel.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    //                根据小说的名字查询小说表中是否有该小说
    public boolean isNovel(String title){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels where title=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Novel.class),title)!=null;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
//    根据关键字搜索小说
    public List<Novel> getSearchCount(String keyword){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels where title like '%"+keyword+"%'";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Novel.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    通过关键字搜索小说
    public List<Novel> getNovelBySearchCount(String keyword) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            // 构建 SQL 查询，搜索标题和作者中包含关键字的记录
            String sql = "SELECT * FROM novels WHERE title LIKE ? OR author LIKE ?";
            // 使用 jdbcTemplate.query 执行查询
            // 传递关键字的 SQL 占位符，一个用于 title，一个用于 author
            return jdbcTemplate.query(sql, new Object[]{"%"+keyword + "%", "%"+keyword + "%"}, new BeanPropertyRowMapper<>(Novel.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
//    根据id获取小说
    public Novel getNovelById(int id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels where novel_id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Novel.class),id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//   添加小说
    public int addNovel(Novel novel){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "insert into novels(title,author,description,cover_image_url,category,statu_s,update_time,click_count) values(?,?,?,?,?,?,NOW(),?)";
            affectedRow = jdbcTemplate.update(sql,novel.getTitle(),novel.getAuthor(),novel.getDescription(),novel.getCover_image_url(),novel.getCategory(),novel.getStatu_s(),novel.getClick_count());
       }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    用户添加小说
    public int userAddNovel(int user_id,Novel novel){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "insert into review(user_id,title,author,description,cover_image_url,category,statu_s,update_time,click_count,status_admin) values(?,?,?,?,?,?,?,NOW(),?,?)";
            affectedRow = jdbcTemplate.update(sql,user_id,novel.getTitle(),novel.getAuthor(),novel.getDescription(),novel.getCover_image_url(),novel.getCategory(),novel.getStatu_s(),0,0);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    查询review表里的数据
    public List<Review> getReviewList(){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from review";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Review.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    查看review表前10条数据
    public List<Review> getReviewCount(){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from review order by click_count desc limit 10";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Review.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    根据review表的id修改当前小说的审核状态
    public int updateReviewStatus(int id,int status){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update review set status_admin=? where id=?";
            affectedRow = jdbcTemplate.update(sql,status,id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    根据审核表的id找到上传的小说信息
    public Review getReviewById(int id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from review where id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Review.class),id);
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
//    更新小说信息
    public int updateNovel(Novel novel){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update novels set title=?,author=?,description=?,cover_image_url=?,category=?,statu_s=?,update_time=? where novel_id=?";
            affectedRow = jdbcTemplate.update(sql,novel.getTitle(),novel.getAuthor(),novel.getDescription(),novel.getCover_image_url(),novel.getCategory(),novel.getStatu_s(),novel.getUpdate_time(),novel.getNovel_id());
        } catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    删除小说
    public int deleteNovelById(int id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "delete from novels where novel_id=?";
            affectedRow = jdbcTemplate.update(sql,id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    根据小说id获取章节列表和对应的章节内容
public List<Chapter> getChapterListAndContent(int id){
    jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    List<Chapter> chapterList = new ArrayList<>();
    try {
        List<Map<String, Object>> result = jdbcTemplate.queryForList("CALL FindNovelById(?)", id);
        for(Map<String, Object> row: result){
            Chapter chapter = new Chapter();
            chapter.setChapter_id((Integer) row.get("chapter_id"));
            chapter.setNovel_id((Integer) row.get("novel_id"));
            chapter.setTitle((String) row.get("title"));
            chapter.setContent((String) row.get("content"));
            chapter.setOrder_number((Integer) row.get("order_number"));

            chapterList.add(chapter);
        }
    }catch (Exception e){
        e.printStackTrace();
    }
    return chapterList;
}

//    添加小说收藏
    public int addbookshelf(int user_id,int novel_id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "insert into bookshelf(user_id,novel_id) values(?,?)";
            affectedRow = jdbcTemplate.update(sql,user_id,novel_id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    获取小说收藏列表
    public List<Novel> getBookshelfList(int user_id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels where novel_id in (select novel_id from bookshelf where user_id=?)";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Novel.class),user_id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    取消收藏
    public int deleteBookshelf(int user_id,int novel_id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "delete from bookshelf where user_id=? and novel_id=?";
            affectedRow = jdbcTemplate.update(sql,user_id,novel_id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return affectedRow;
    }
//    根据小说id判断是否已经收藏，已经收藏返回true，否则返回false
    public boolean isFavorite(int user_id,int novel_id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from bookshelf where user_id=? and novel_id=?";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql,user_id,novel_id);
            if(result.size()>0){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
//    通过小说的点击量来排序，选出前10本小说
    public List<Novel> getClickCount(){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select * from novels order by click_count desc limit 10";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Novel.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    根据小说id对点击数加1
    public int updateClickCount(int novel_id){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update novels set click_count=click_count+1 where novel_id=?";
            return jdbcTemplate.update(sql,novel_id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
//    查看全部小说，每页10本多余分页
    public List<Novel> getNovelsByPage(int pageNumber, int pageSize){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            int offset = (pageNumber - 1) * pageSize; // 计算SQL查询的OFFSET值
            String sql = "SELECT * FROM novels ORDER BY novel_id LIMIT ? OFFSET ?";
            return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Novel.class),pageSize,offset);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
//    获取小说的总数
    public int getTotalCount(){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "select count(*) from novels";
            return jdbcTemplate.queryForObject(sql,Integer.class);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    //保存订单记录的信息
    public boolean saveOrderRecord(OrderRecord orderRecord){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "insert into order_record(order_no,user_id,novel_name,chapter_id,chapter_title,price,status,create_time) values(?,?,?,?,?,?,?,?)";
            affectedRow = jdbcTemplate.update(sql,orderRecord.getOrder_no(),orderRecord.getUser_id(),orderRecord.getNovel_name(),orderRecord.getChapter_id(),orderRecord.getChapter_title(),orderRecord.getPrice(),orderRecord.getStatus(),orderRecord.getCreate_time());
            if(affectedRow>0)
                return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
//    更新订单状态
    public boolean updateOrderStatus(String order_no,String status){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "update order_record set status=? where order_no=?";
            affectedRow = jdbcTemplate.update(sql,status,order_no);
            if(affectedRow>0)
                return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
//    根据小说的id和章节id获取对应章节
    public Chapter getChapterById(int chapterId,int novelId){
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        try {
            sql = "CALL FindChapterByIds(?,?)";
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Chapter.class),chapterId,novelId);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}
